<?php
/**
 * FBA每日库存报表(erp)
 * (model)
 */

namespace App\Models\SalesReport;

use App\Models\Basics\SysPlatAccount;
use App\Models\Basics\SysCountry;
use App\Models\Basics\SysArea;
use App\Models\Product\ProductClassify;
use Illuminate\Database\Eloquent\Model;
use App\Models\Product\ProductSku;
use Illuminate\Support\Facades\DB;

class FBAInventory extends Model
{
    protected $table = 'sr_fba_inventory';

    protected $guarded = [];

    //public $timestamps = false;

    public function account(){
        return $this->belongsTo(SysPlatAccount::class,'account_id');
    }

    public function sku(){
        return $this->belongsTo(ProductSku::class,'psku_id');
    }

    public function country(){
        return $this->belongsTo(SysCountry::class,'country_id');
    }

    public function area(){
        return $this->belongsTo(SysArea::class,'area_id');
    }

    public static function headersField(){
        $fields = [];
        $fields['account'] = ['title' => '账号','sumField' => false];
        $fields['area'] = ['title' => '站点','sumField' => false];
        $fields['country'] = ['title' => '国家','sumField' => false];
        $fields['seller_sku'] = ['title' => '平台SKU','sumField' => false];
        $fields['fnsku'] = ['title' => 'FNSKU','sumField' => false];
        $fields['asin'] = ['title' => 'ASIN','sumField' => false];
        $fields['sku'] = ['title' => 'SKU','sumField' => false];
        $fields['sku_cn'] = ['title' => '产品名称','sumField' => false];
        $fields['classify'] = ['title' => '品类','sumField' => false];
        $fields['season'] = ['title' => '季节性','sumField' => false];
        $fields['total_quantity'] = ['title' => '总库存(可发+保留+在途)','sumField' => true];
        $fields['quantity'] = ['title' => '备货参考库存(可发+保留)','sumField' => true];
        $fields['fulfillable_quantity'] = ['title' => '可发库存','sumField' => true];
        $fields['reserved_quantity'] = ['title' => '保留库存','sumField' => true];
        $fields['inbound'] = ['title' => '在途库存','sumField' => true];
        $fields['amount'] = ['title' => '货值(¥)','sumField' => true];
        return $fields;
    }

    public static function getDownloadData($re){
        foreach ($re as $row){
            $temp = [];
            $temp['account'] = $row->account->account ?? '';
            $temp['area'] = $row->area->area ?? '';
            $temp['country'] = $row->country->country ?? '';
            $temp['seller_sku'] = $row->seller_sku;
            $temp['fnsku'] = $row->fnsku;
            $temp['asin'] = $row->asin;
            $temp['psku_code'] = $row->sku->psku_code ?? '';
            $temp['psku_cn'] = $row->sku->psku_cn ?? '';
            $temp['classify'] = $row->sku->classify->title ?? '';
            $temp['season'] = ProductClassify::$_season[$row->sku->classify->season ?? 3];
            $temp['quantity'] = $row->quantity;
            $temp['total_quantity'] = $row->total_quantity;
            $temp['fulfillable_quantity'] = $row->fulfillable_quantity;
            $temp['reserved_quantity'] = $row->reserved_quantity;
            $temp['inbound'] = $row->inbound;
            $temp['amount'] = $row->amount;
            yield $temp;
        }
    }

    /**
     * FBA在库在途（单个国家）
     * @param $countryId
     * @param $pSkuId
     * @return mixed
     */
    public static function _getInventoryFind($countryId,$pSkuId){
        return self::query()
            ->select(DB::raw('sum(quantity) as quantity,sum(inbound) as inbound'))
            ->where('country_id',$countryId)
            ->where('psku_id',$pSkuId)
            ->first();
    }

    /**
     * FBA在库在途（多个国家）
     * @param $countryIds
     * @param $pSkuId
     * @return mixed
     */
    public static function _getInventory($countryIds,$pSkuId){
        return self::query()
            ->select(DB::raw('sum(quantity) as quantity,sum(inbound) as inbound'))
            ->whereIn('country_id',$countryIds)
            ->where('psku_id',$pSkuId)
            ->first();
    }

    /**
     * 获取平台sku编码 （发货站点）
     * @param $account_id
     * @param $area_id
     * @param $psku_id
     * @return mixed|string
     */
    public static function getAreaSellerSku($account_id, $area_id, $psku_id){
        $res = self::query()
            ->where('account_id', $account_id)
            ->where('area_id', $area_id)
            ->where('psku_id', $psku_id)
            ->value('seller_sku');
        return $res ?? '';
    }

    /**
     * 依据站点id和产品skuid,获取站点下的账号、FBA可发库存 (全部)
     * @param $areaIds
     * @param $pSkuId
     * @param $maxNum
     * @return \Illuminate\Database\Eloquent\Builder[]|\Illuminate\Database\Eloquent\Collection
     */
    public static function getFBAInventory($areaIds, $pSkuId, $maxNum = 0){
        return self::query()
            ->whereIn('area_id', $areaIds)
            ->where('psku_id', $pSkuId)
            ->select(DB::raw('account_id, area_id,seller_sku,sum(fulfillable_quantity) as num'))
            ->groupBy('account_id', 'area_id','seller_sku')
            ->orderBy('num', 'DESC')
            ->having('num', '>=', $maxNum)
            ->get();
    }

    public static function getFBAInventoryRakuten($areaId, $pSkuId, $accountId = 0){
        $query =  self::query();
        $query->where('area_id', $areaId);
        $query->where('psku_id', $pSkuId);
        $query->where('account_id', $accountId);
        return $query->first();
    }

    /**
     * 根据skuid和站点id参数,获取站点下的账号、库存 (单个)
     * @param $accountId
     * @param $areaId
     * @param $pSkuId
     * @return int|mixed
     */
    public static function getACccountFbaInventory($accountId, $areaId, $pSkuId){
        $re = FBAInventory::query()
            ->where('account_id', $accountId)
            ->where('area_id', $areaId)
            ->where('psku_id', $pSkuId)
            ->max('fulfillable_quantity');
        return $re ?: 0;
    }

    /**
     * 根据skuid和站点id参数,获取站点下的账号、可用库存
     * @param $areaId
     * @param $pSkuId
     * @return array
     */
    public static function getACccountOptionsForFBA($areaId, $pSkuId){
        $res = FBAInventory::query()
            ->where('area_id', $areaId)
            ->where('psku_id', $pSkuId)
            ->where('fulfillable_quantity','>',0)
            ->get();
        $data = [];
        $arr = [];
        foreach($res as $re){
            $key = $re->account_id.'_'.$re->area_id.'_'.$re->psku_id;
            if(!array_key_exists($key, $arr)){
                $arr[$key] = $re->fulfillable_quantity;
                $data[$key] = ['seller_sku' => $re->seller_sku,'account' => $re->account->account];
            }else{
                if($arr[$key] < $re->fulfillable_quantity){
                    $arr[$key] = $re->fulfillable_quantity;
                    $data[$key] = ['seller_sku' => $re->seller_sku,'account' => $re->account->account];
                }
            }
        }
        $result = [];
        foreach($data as $key => $_data){
            list($account_id,$area_id,$psku_id) = explode('_',$key);
            $result[$account_id.'_'.$_data['seller_sku']] = $_data['account'];
        }
        return $result;
    }

    /**
     * 验证,获取fba可发库存及账号
     * @param array $orderItemSku
     * @param int $countryId
     * @param int $areaId
     * @param int $platId
     * @return array
     */
    public static function getInstantMaxAndAccountId($orderItemSku = [], $countryId = 0, $areaId = 0, $platId = 0){
        /*
        $orderItemSku = [
            0 => [
                "psku_id" => 1934,
                "units" => 1
            ],
            1 => [
                "psku_id" => 1933,
                "units" => 1
            ]
        ];
        */
        if ($orderItemSku) {
            $pSkuIds = array_column($orderItemSku,'psku_id');
        }
        $query = self::query()->selectRaw('psku_id,fulfillable_quantity,account_id');
        if ($pSkuIds) $query->whereIn('psku_id', $pSkuIds);
        if ($countryId > 0) $query->where('country_id', $countryId);
        if ($areaId > 0) $query->where('area_id', $areaId);
        if ($platId > 0) $query->where('plat_id', $platId);
        $query->groupBy('account_id','psku_id');
        $fbaInventory = $query->get();

        $inventoryFlag = false; //默认库存状态
        $platAccountId = 0; //默认账号ID
        if ($fbaInventory->isEmpty()){
            return [$inventoryFlag,$platAccountId];
        }

        //1.获取FBA账号下产品的库存数量
        $accountQuantity = [];
        foreach ($fbaInventory as $item) {
            $accountId = $item->account_id;
            $pskuId = $item->psku_id;
            $quantity = $item->fulfillable_quantity;

            if (!isset($accountQuantity[$accountId])) {
                $accountQuantity[$accountId] = [];
            }
            $accountQuantity[$accountId][$pskuId] = $quantity;
        }

        //2.筛选满足订单发货数量的FBA账号
        /*$accountQuantity = [
            35 => [
                1933 => 8,
                1934 => 107
            ],
            50 => [
                1933 => 1,
                1934 => 23
            ],
        ];*/
        if ($accountQuantity) {
            //格式订单sku数据
            $orderSkuUnit = [];
            foreach ($orderItemSku as $orderItems) {
                $orderSkuUnit[$orderItems['psku_id']] = $orderItems['units'];
            }

            $accountArr  = [];
            foreach ($accountQuantity as $accountId => $pskuQuantity) {
                $quantityFlag = true;
                $totalQuantity = 0;
                foreach ($orderSkuUnit as $pskuId => $units) {
                    if (!isset($pskuQuantity[$pskuId]) || $pskuQuantity[$pskuId] < $units) {
                        $quantityFlag = false;
                        break;
                    }
                    $totalQuantity += $pskuQuantity[$pskuId];
                }
                if ($quantityFlag) {
                    $accountArr[$accountId] = $totalQuantity;
                }
            }

            /*
            $accountArr = [
                35 => 115,
                50 => 90,
                51 => 121
            ];
            */
            if ($accountArr) {
                arsort($accountArr); //倒序,依据账号下的库存数量排序(从大到小)
                $inventoryFlag = true; //库存状态
                $platAccountId = array_keys($accountArr)[0]; //取第一条记录的键(account_id)
            }
        }

        return [$inventoryFlag,$platAccountId];
    }

    /**
     * FBA在库（模型）
     * @param array $areaId
     * @param int $pSkuId
     * @param int $platId
     * @param int $accountId
     * @param string $str
     * @return \Illuminate\Database\Eloquent\Builder|Model|object|null
     */
    public static function getInstantMaxFind($areaId = 0, $pSkuId = 0, $platId = 0, $accountId = 0, $str = 'instant'){
        $query = self::query();
        if ($areaId > 0) $query->where('area_id', $areaId);
        if ($pSkuId > 0) $query->where('psku_id', $pSkuId);
        if ($platId > 0) $query->where('plat_id', $platId);
        if ($accountId > 0) $query->where('account_id', $accountId);

        $query->orderByDesc('fulfillable_quantity');
        return $query->first();
    }

    /**
     * 验证JP-FBA库存 (验证FBA库存)
     * @param $rakutenOrder
     * @return array|false
     */
    public static function _checkJpFbaInventoryFnsku($rakutenOrder){
        if (!$rakutenOrder) return [false, 0];

        //验证平台sku
        $orderItemSku = [];
        foreach ($rakutenOrder->rakuten_orders_items as $_k => $_v) {
            $pskuId = ProductSku::getValueByCode($_v->variant_id, 'id'); //公司sku
            if (!$pskuId){
                break;
            }
            $orderItemSku[$_k]['psku_id'] = $pskuId;
            $orderItemSku[$_k]['units'] = $_v->units ?? 0;
        }
        if (count($orderItemSku) != count($rakutenOrder->rakuten_orders_items)) return [false, 0];

        //验证FBA库存(默认JP国家ID: 12)
        list($inventoryFlag,$accountId) = self::getInstantMaxAndAccountId($orderItemSku,12);

        return [$inventoryFlag,$accountId];
    }

    /**
     * J更新P-FBA库存 (扣减FBA库存)
     * @param $rakutenOrder
     * @return bool
     */
    public static function _saveJpFbaInventoryFnsku($rakutenOrder){
        if (!$rakutenOrder) return false;

        $inventoryFlag = false;
        foreach ($rakutenOrder->rakuten_orders_items as $_k => $_v){
            $pskuId = ProductSku::getValueByCode($_v->variant_id, 'id');
            if (!$pskuId){
                $inventoryFlag = false;
                break;
            }

            //海外仓(代发)库存
            $fbaInventory = FBAInventory::getInstantMaxFind(SysArea::AREA_JP,$pskuId);
            if (!$fbaInventory){
                $inventoryFlag = false;
                break;
            }
            if (intval($fbaInventory->fulfillable_quantity) < intval($_v->units)){
                $inventoryFlag = false;
                break;
            }
            $fbaInventory->fulfillable_quantity -= intval($_v->units); //扣减(可用库存)
            $flag = $fbaInventory->save();
            if (!$flag){
                $inventoryFlag = false;
                break;
            }

            $inventoryFlag = true;
        }

        return $inventoryFlag;
    }

}
